
/*
'===============================================================================
'  HNG.Portal.BL.TblMenuManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HNG.Portal.BLL
{
	public class TblMenuManager : TblMenuManagerBase
	{
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
		/// <summary>
		/// Purpose: Class constructor.
		/// </summary>
		public TblMenuManager()
		{
			// Nothing for now.
		}

        public DataTable Get_Bykeysearch(string keysearch, string columnName)
        {
            string strQuery = "select * from TblMenu ";
            if (columnName != "")
                strQuery += " where " + columnName + " like N'%" + keysearch + "%'" + " Order by ID desc";
            else
                strQuery += " where TenMenu like N'%" + keysearch + "%'" + " Order by ID desc";
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, strQuery);
        }

        public DataTable SelectCommon(TblMenuEntity _TblMenuEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblMenuEntity.SortExpression) ? "[CreateDate]" : _TblMenuEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblMenuEntity.SortDirection) ? " DESC" : _TblMenuEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[ID],").Append("\n");
                sbSql.Append("  ch.[LangID],").Append("\n");
                sbSql.Append("  ch.[Danhmuccha],").Append("\n");
                sbSql.Append("  ch.[Type],").Append("\n");
                sbSql.Append("  ch.[TenMenu],").Append("\n");
                sbSql.Append("  ch.[RoleID],").Append("\n");
                sbSql.Append("  ch.[Image],").Append("\n");
                sbSql.Append("  ch.[Url],").Append("\n");
                sbSql.Append("  ch.[TrangThai],").Append("\n");
                sbSql.Append("  lng.[Name] LangName").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblBanner ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblLang lng").Append("\n");
                sbSql.Append("  ON lng.ID = ch.LangID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (_TblMenuEntity.Id != 0)
                {
                    sbSql.AppendFormat("  AND ch.[ID] LIKE {0}", _TblMenuEntity.Id).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[LangID] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Danhmuccha] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Type] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenMenu] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[RoleID] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Image] LIKE N'%' + {0} + '%'", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Url] LIKE N'%' + {0} + '%')", _TblMenuEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%' + {0} + '%')", _TblMenuEntity.Condition).Append("\n");

                }

                if (_TblMenuEntity.LangId != 0 || _TblMenuEntity.LangId != null)
                {
                    sbSql.AppendFormat("  AND ch.[LangId] ={0}", _TblMenuEntity.LangId).Append("\n");
                }

                if (_TblMenuEntity.RoleId != 0 || _TblMenuEntity.RoleId != null)
                {
                    sbSql.AppendFormat("  AND ch.[RoleId] ={0}", _TblMenuEntity.RoleId).Append("\n");
                }

                if (_TblMenuEntity.Danhmuccha != -1)
                {
                    sbSql.AppendFormat("  AND ch.[DanhMucCha] ={0}", _TblMenuEntity.Danhmuccha).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblMenuEntity.Type))
                {
                    sbSql.AppendFormat("  AND ch.[type] ={0}", _TblMenuEntity.Type).Append("\n");
                }

                if (_TblMenuEntity.TrangThai.ToString().Equals("True"))
                {
                    sbSql.AppendFormat("  AND ch.[TrangThai] ={0}", 1).Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblMenuEntity.StartIndex, _TblMenuEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString());
        }
	}
}
